package core.report.yearReport.util;

import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;

import core.report.yearReport.bean.BaseExcelReportDataBean;
import core.report.yearReport.bean.MergedBean;

public class ExportExcelUtil {

	public static final int PERCENT_TYPE = 0;
	public static final int INTEGER_TYPE = 1;
	public static final int LONG_TYPE = 2;
	public static final int DECIMAL_TYPE = 3;
	public static final int TEXT_TYPE = 4;
	public static final int FORMULA_TYPE = 5;
	
	private static final BigDecimal ONE_HUNDRED = new BigDecimal(100);

	@SuppressWarnings("unchecked")
	public void exportExcel(String title, List<MergedBean> headers,
			Collection<BaseExcelReportDataBean> dataset, OutputStream out,
			int beginIndex, int colCount, String pattern) {
		// 声明一个工作薄
		Workbook workbook = new HSSFWorkbook();

		// 生成一个表格
		Sheet sheet = workbook.createSheet(title);
		// 设置表格默认列宽度为15个字节
		sheet.setDefaultColumnWidth((short) 15);
		
		sheet.setColumnWidth((short) 0, (short) (35.7*200));//200个像素
		
		// 自适应宽度
//		sheet.autoSizeColumn(1);
		// sheet.autoSizeColumn(1, true);
		// 生成一个样式
		CellStyle headerStyle = workbook.createCellStyle();
		// 设置这些样式
		
		headerStyle.setFillForegroundColor(HSSFColor.WHITE.index);
		headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		// 生成一个字体
		Font font = workbook.createFont();
		font.setColor(HSSFColor.BLACK.index);
		font.setFontHeightInPoints((short) 12);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		// 把字体应用到当前的样式
		headerStyle.setFont(font);
		// 生成并设置另一个样式
		CellStyle style1 = workbook.createCellStyle();
		style1.setFillForegroundColor(HSSFColor.WHITE.index);
		style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style1.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style1.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		// 生成另一个字体
		Font font1 = workbook.createFont();
		font1.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		// 把字体应用到当前的样式
		style1.setFont(font1);
		
		// 生成并设置另一个样式
		CellStyle style2 = workbook.createCellStyle();
		style2.setFillForegroundColor(HSSFColor.YELLOW.index);
		style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		// 生成另一个字体
		Font font2 = workbook.createFont();
		font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		// 把字体应用到当前的样式
		style2.setFont(font2);

		// 产生表格标题行
		Row row = null;
		for (MergedBean mergedBean : headers) {
			CellRangeAddress ce = new CellRangeAddress(
					mergedBean.getFirstRow(), mergedBean.getLastRow(),
					mergedBean.getFirstCol(), mergedBean.getLastCol());
			sheet.addMergedRegion(ce);
			row = sheet.getRow(mergedBean.getFirstRow());
			if (row == null) {
				row = sheet.createRow(mergedBean.getFirstRow());
			}
			row.setHeight((short)(35.7*10));//10个像素
			row.setRowStyle(headerStyle);
			Cell cell = row.createCell(mergedBean.getFirstCol());
			cell.setCellStyle(headerStyle);
			HSSFRichTextString text = new HSSFRichTextString(mergedBean
					.getValue());
			cell.setCellValue(text);
		}
		
		CellStyle dataStyle = null; 
		// 遍历集合数据，产生数据行
		Iterator<BaseExcelReportDataBean> it = dataset.iterator();
		while (it.hasNext()) {
			row = sheet.createRow(beginIndex);
			BaseExcelReportDataBean data = it.next();
			if(data.getAttributeFlag()){
				dataStyle = style2;
			}else{
				dataStyle = style1;
			}
			for (int i = 0; i < colCount; i++) {
				Cell cell = row.createCell(i);
				cell.setCellStyle(dataStyle);
				String textValue = data.getValueByIndex(i);
				int formatType = data.getFormatTypeByIndex(i);
				// public final static int PERCENT_TYPE=0;
				// public final static int INTEGER_TYPE=1;
				// public final static int LONG_TYPE=2;
				// public final static int DECIMAL_TYPE=3;
				// public final static int TEXT_TYPE=4;
				// public final static int FORMULA_TYPE=5;

				// 利用正则表达式判断textValue是否全部由数字组成
				BigDecimal bigDecimal = null;
				if (textValue != null) {
					switch (formatType) {
					case PERCENT_TYPE:
						try{
						bigDecimal = new BigDecimal(textValue);
						cell.setCellValue(bigDecimal.multiply(ONE_HUNDRED).setScale(2,
								BigDecimal.ROUND_HALF_UP).toPlainString() + "%");
						}catch(NumberFormatException e){
//							System.out.println("textValue:"+textValue);
//							e.printStackTrace();
						}
						break;
					case INTEGER_TYPE:
						bigDecimal = new BigDecimal(textValue);
						cell.setCellValue(bigDecimal.setScale(0,BigDecimal.ROUND_HALF_UP).toPlainString());
						break;
					case LONG_TYPE:
						bigDecimal = new BigDecimal(textValue);
						cell.setCellValue(bigDecimal.setScale(0,BigDecimal.ROUND_HALF_UP).toPlainString());
						break;
					case DECIMAL_TYPE:
						bigDecimal = new BigDecimal(textValue);
						bigDecimal = bigDecimal.setScale(2,
								BigDecimal.ROUND_HALF_UP);
						cell.setCellValue(bigDecimal.toPlainString());
						break;
					case TEXT_TYPE:
						RichTextString richString = new HSSFRichTextString(
								textValue);
						cell.setCellValue(richString);
						break;
					case FORMULA_TYPE:
						//暂时无公式的
						break;
					}
				} else {
					cell.setCellValue("");
				}
			}
			beginIndex++;
		}
		try {
			workbook.write(out);
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (out != null) {
				try {
					out.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}

	}
}
